package com.turing.post.report.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import com.turing.post.report.util.MerchantDaySettlement;
import com.turing.post.report.util.MerchantDaySettlementDao;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

@Stateless(mappedName = "report.impl.MerchantDaySettlementDaoImpl")
@Remote
@Local
public class MerchantDaySettlementDaoImpl implements MerchantDaySettlementDao {
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	/**
	 * 创建商户日结汇总记录
	 * 
	 * @param mds
	 * @throws PCPSApplicationException
	 */
	@Override
	public void insert(MerchantDaySettlement mds)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, mds);
	}

	/**
	 * 修改商户日结汇总记录
	 * 
	 * @param ids
	 * @throws PCPSApplicationException
	 */
	@Override
	public void update(MerchantDaySettlement mds)
			throws PCPSApplicationException {
		GeneralDao.update(_em, mds);
	}

	/**
	 * 删除某发卡商某日所有商户的日结数据
	 * 
	 * @param issueCompanyNo
	 *            发卡商号
	 * @param tranDate
	 *            帐务日期
	 */
	@TransactionAttribute(javax.ejb.TransactionAttributeType.REQUIRES_NEW)
	public void removeAllMerchantDaySettlementByTranDate(String issueCompanyNo,
			Date tranDate) {
		Query query = _em
				.createNativeQuery("delete from MerchantDaySettlement t "
						+ "where t.issueCompanyNo = ?1 and t.tranDate = ?2");
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, tranDate);
		query.executeUpdate();
	}

	/**
	 * 得到某日所有有扎帐数据的商户号集合
	 * 
	 * @param tranDate
	 * @return List<String[]>
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getHaveMerchantDaySettlementMerchantno(Date trandate) {
		String sql = "select distinct(merchantno) from MerchantDaySettlement where trandate=?1";
		Query query = _em.createNativeQuery(sql, String.class);
		query.setParameter(1, trandate);
		List<String> merchantnolist = query.getResultList();
		return merchantnolist;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getMerchantDaySettlementCountByCondition(
			PCPSWebInput webInput, String merchantNo, String startDate,
			String endDate, String reportType) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(reportType))
			return null;
		String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "";
		// 统计表
		if ("0".equals(reportType)) {
			sql = "select m.merchantno,sum(m.Cost) PaidFund,sum(m.Deposit) Deposit,sum(m.Cost-m.Deposit) Difference,m.trandate"
					+ " from MerchantDaySettlement m"
					+ " where m.IssueCompanyNo=?1"
					+ " and (?2 is null or m.merchantno = ?2)"
					+ " and m.trandate>=to_date(?3,'yyyy-mm-dd')"
					+ " and m.trandate<=to_date(?4,'yyyy-mm-dd')"
					+ " group by m.merchantno,m.trandate order by m.trandate,m.merchantno";
		}
		// 汇总表
		else {
			sql = "select m.merchantno,sum(m.Cost) Consume,sum(m.Deposit) Deposit,sum(m.Cost-m.Deposit) Difference"
					+ " from MerchantDaySettlement m"
					+ " where m.IssueCompanyNo=?1"
					+ " and (?2 is null or m.merchantno = ?2)"
					+ " and m.trandate>=to_date(?3,'yyyy-mm-dd')"
					+ " and m.trandate<=to_date(?4,'yyyy-mm-dd')"
					+ " group by m.merchantno order by m.merchantno";
		}
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, merchantNo);
		query.setParameter(3, startDate);
		query.setParameter(4, endDate);
		List<Object[]> list = query.getResultList();
		return list;
	}

	/**
	 * 得到某商户某日的日结数据
	 * 
	 * @param merchantno
	 * @param tranDate
	 */

	@SuppressWarnings("unchecked")
	@Override
	public MerchantDaySettlement getMerchantDaySettlementByTranDate_Merchantno(
			String merchantno, Date tranDate) {
		String sql = "select * from merchantdaysettlement t "
				+ "where t.merchantno = ?1 " + "and t.trandate=?2";
		Query query = _em.createNativeQuery(sql, MerchantDaySettlement.class);
		query.setParameter(1, merchantno);
		query.setParameter(2, tranDate);
		List<MerchantDaySettlement> list = query.getResultList();
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	/**
	 * 根据商户号，时间段，得到某段时间最后一天之前的消费总额
	 * 
	 * @param merchantno
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	@Override
	public Long getLimitAmountByDateDecreaseOneDay(String merchantno,
			Date startdate, Date enddate) {
		String sql = "select nvl(sum(Consume-CancelPaid),0) from MerchantDaySettlement where merchantno=?1 and trandate between ?2 and ?3-1";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantno);
		query.setParameter(2, startdate);
		query.setParameter(3, enddate);
		return new BigInteger(query.getSingleResult().toString()).longValue();
	}

	/**
	 * 根据商户号，时间段，得到某段时间的消费总额
	 * 
	 * @param merchantno
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	@Override
	public Long getLimitAmountByDate(String merchantno, Date startdate,
			Date enddate) {
		String sql = "select nvl(sum(Consume-CancelPaid),0) from MerchantDaySettlement where merchantno=?1 and trandate between ?2 and ?3";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantno);
		query.setParameter(2, startdate);
		query.setParameter(3, enddate);
		return new BigInteger(query.getSingleResult().toString()).longValue();
	}
	
	/**
	 * 根据商户号，时间段，得到某段时间的收入总额
	 * 
	 * @param merchantno
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	@Override
	public Long getAllIncomeByDate(String merchantno, Date startdate,
			Date enddate) {
		String sql = "select nvl(sum(income),0) from MerchantDaySettlement where merchantno=?1 and trandate between ?2 and ?3";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantno);
		query.setParameter(2, startdate);
		query.setParameter(3, enddate);
		return new BigInteger(query.getSingleResult().toString()).longValue();
	}

	/**
	 * 根据四要素检索商户交易流水-Mis
	 * 
	 * @param tranDate
	 *            交易日期
	 * @param tranDate
	 *            商户
	 * @return 商户交易流水对象列表
	 * 
	 */
	List<MerchantDaySettlement> mislist = null;

	public List<MerchantDaySettlement> getMerchantDaySettlementMisTotal(
			PCPSWebInput webInput, String tranTime, String merchantNoStr) {
		mislist = null;
		mislist = new ArrayList<MerchantDaySettlement>();
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(tranTime))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		getgetMerchantDaySettlementTotalDayMis(tranTime, issuecompanyno,
				merchantNoStr);
		getgetMerchantDaySettlementTotalTopDayMis(tranTime, issuecompanyno,
				merchantNoStr);
		return mislist;
	}

	/**
	 * 根据四要素检索商户交易流水-Mis
	 * 
	 * @param tranDate
	 *            选择交易日期
	 * @return 商户交易流水对象列表 *
	 */
	@SuppressWarnings("unchecked")
	private void getgetMerchantDaySettlementTotalDayMis(String tranTime,
			String issuecompanyno, String merchantNoStr) {
		String sql = "select sum(ConsumeCount)as ConsumeCount,sum(Consume)as Consume from  MerchantDaySettlement  where  MerchantNo in("
				+ merchantNoStr
				+ ") and IssueCompanyNo=?1 and trandate=to_date(?2,'yyyy-mm-dd') ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, tranTime);
		List<Object[]> objs = query.getResultList();
		MerchantDaySettlement merchantDaySettlement = new MerchantDaySettlement();
		merchantDaySettlement.setIssueCompanyNo("0");
		merchantDaySettlement.setMerchantNo("今日数量");
		if (null != objs && objs.size() > 0) {
			Object[] obj = objs.get(0);
			if (obj[0] != null)
				merchantDaySettlement.setConsumeCount(new BigInteger(obj[0]
						.toString()));
			else
				merchantDaySettlement.setConsumeCount(new BigInteger("0"));
			if (obj[1] != null)
				merchantDaySettlement.setConsume(new BigInteger(obj[1]
						.toString()));
			else
				merchantDaySettlement.setConsume(new BigInteger("0"));
		} else {
			merchantDaySettlement.setConsumeCount(new BigInteger("0"));
			merchantDaySettlement.setConsume(new BigInteger("0"));
		}
		mislist.add(merchantDaySettlement);
	}

	/**
	 * 根据四要素检索商户交易流水_Mis
	 * 
	 * @param tranDate
	 *            选择交易日期的前一天累计
	 * @return 商户交易流水对象列表 *
	 */
	@SuppressWarnings("unchecked")
	private void getgetMerchantDaySettlementTotalTopDayMis(String tranTime,
			String issuecompanyno, String merchantNoStr) {
		String sql = "select sum(ConsumeCount)as ConsumeCount,sum(Consume)as Consume from  MerchantDaySettlement  where MerchantNo in("
				+ merchantNoStr
				+ ") and  IssueCompanyNo=?1 and trandate<to_date(?2,'yyyy-mm-dd') ";

		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, tranTime);
		List<Object[]> objs = query.getResultList();
		MerchantDaySettlement merchantDaySettlement = new MerchantDaySettlement();
		merchantDaySettlement.setIssueCompanyNo("1");
		merchantDaySettlement.setMerchantNo("昨日累计");
		if (null != objs && objs.size() > 0) {
			Object[] obj = objs.get(0);
			if (obj[0] != null)
				merchantDaySettlement.setConsumeCount(new BigInteger(obj[0]
						.toString()));
			else
				merchantDaySettlement.setConsumeCount(new BigInteger("0"));
			if (obj[1] != null)
				merchantDaySettlement.setConsume(new BigInteger(obj[1]
						.toString()));
			else
				merchantDaySettlement.setConsume(new BigInteger("0"));
		} else {
			merchantDaySettlement.setConsumeCount(new BigInteger("0"));
			merchantDaySettlement.setConsume(new BigInteger("0"));
		}
		mislist.add(merchantDaySettlement);
	}

	/**
	 * 根据四要素检索商户交易流水-Pos
	 * 
	 * @param tranDate
	 *            交易日期
	 * @return 商户交易流水对象列表
	 */

	List<MerchantDaySettlement> poslist = null;

	public List<MerchantDaySettlement> getMerchantDaySettlementPosTotal(
			PCPSWebInput webInput, String tranTime, String merchantNoStr) {
		poslist = null;
		poslist = new ArrayList<MerchantDaySettlement>();
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(tranTime))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		getgetMerchantDaySettlementTotalDayPos(tranTime, issuecompanyno,
				merchantNoStr);
		getgetMerchantDaySettlementTotalTopDayPos(tranTime, issuecompanyno,
				merchantNoStr);
		return poslist;
	}

	/**
	 * 根据四要素检索商户交易流水-Pos
	 * 
	 * @param tranDate
	 *            选择交易日期
	 * @return 商户交易流水对象列表
	 * 
	 */
	@SuppressWarnings("unchecked")
	private void getgetMerchantDaySettlementTotalDayPos(String tranTime,
			String issuecompanyno, String merchantNoStr) {
		String sql = "select sum(ConsumeCount+DepositCount+CancelPaidCount)as ConsumeCount,sum(Consume-Deposit-CancelPaid)as Consume from  MerchantDaySettlement  where  MerchantNo in("
				+ merchantNoStr
				+ ") and IssueCompanyNo=?1 and trandate=to_date(?2,'yyyy-mm-dd') ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, tranTime);
		List<Object[]> objs = query.getResultList();
		MerchantDaySettlement merchantDaySettlement = new MerchantDaySettlement();
		merchantDaySettlement.setIssueCompanyNo("0");
		merchantDaySettlement.setMerchantNo("今日数量");
		if (null != objs && objs.size() > 0) {
			Object[] obj = objs.get(0);
			if (obj[0] != null)
				merchantDaySettlement.setConsumeCount(new BigInteger(obj[0]
						.toString()));
			else
				merchantDaySettlement.setConsumeCount(new BigInteger("0"));
			if (obj[1] != null)
				merchantDaySettlement.setConsume(new BigInteger(obj[1]
						.toString()));
			else
				merchantDaySettlement.setConsume(new BigInteger("0"));
		} else {
			merchantDaySettlement.setConsumeCount(new BigInteger("0"));
			merchantDaySettlement.setConsume(new BigInteger("0"));
		}
		poslist.add(merchantDaySettlement);
	}

	/**
	 * 根据四要素检索商户交易流水-Pos
	 * 
	 * @param tranDate
	 *            选择交易日期的前一天累计
	 * @return 商户交易流水对象列表
	 * 
	 */
	@SuppressWarnings("unchecked")
	private void getgetMerchantDaySettlementTotalTopDayPos(String tranTime,
			String issuecompanyno, String merchantNoStr) {
		String sql = "select sum(ConsumeCount+DepositCount+CancelPaidCount)as ConsumeCount,sum(Consume-Deposit-CancelPaid)as Consume from  MerchantDaySettlement  where MerchantNo in("
				+ merchantNoStr
				+ ") and  IssueCompanyNo=?1 and trandate<to_date(?2,'yyyy-mm-dd') ";

		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, tranTime);
		List<Object[]> objs = query.getResultList();
		MerchantDaySettlement merchantDaySettlement = new MerchantDaySettlement();
		merchantDaySettlement.setIssueCompanyNo("1");
		merchantDaySettlement.setMerchantNo("昨日累计");
		if (null != objs && objs.size() > 0) {
			Object[] obj = objs.get(0);
			if (obj[0] != null)
				merchantDaySettlement.setConsumeCount(new BigInteger(obj[0]
						.toString()));
			else
				merchantDaySettlement.setConsumeCount(new BigInteger("0"));
			if (obj[1] != null)
				merchantDaySettlement.setConsume(new BigInteger(obj[1]
						.toString()));
			else
				merchantDaySettlement.setConsume(new BigInteger("0"));
		} else {
			merchantDaySettlement.setConsumeCount(new BigInteger("0"));
			merchantDaySettlement.setConsume(new BigInteger("0"));
		}
		poslist.add(merchantDaySettlement);
	}

	/**
	 * 根据西邮寄分公司渠道交易汇总表--报表
	 * 
	 * @param merchantno
	 *            商户号
	 * @param startTime
	 *            起始日期
	 * @param endTime
	 *            终止日期
	 * @return 西邮寄分公司渠道交易汇总表对象列表
	 * 
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getMerchantDaySettlementByMerchant(
			PCPSWebInput webInput, String merchantno, String startTime,
			String endTime) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(startTime) || PCPSUtil.isEmpty(endTime))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select sum(Consume)-sum(CancelPaid),sum(ConsumeCount)+sum(CancelPaidCount),MERCHANTNO,TranDate from MerchantDaySettlement"
				+ " where IssueCompanyNo=?1 and TranDate>=to_date(?2,'yyyy-mm-dd') and TranDate<=to_date(?3,'yyyy-mm-dd')"
				+ " and (?4 is null or MerchantNo = ?4)";
		sql = sql + " group by TranDate,MERCHANTNO order by TranDate desc";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, startTime);
		query.setParameter(3, endTime);
		query.setParameter(4, merchantno);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	/**
	 * 根据西邮寄卡交易结算汇总表--报表
	 * 
	 * @param merchantno
	 *            商户号
	 * @param startTime
	 *            起始日期
	 * @param endTime
	 *            终止日期
	 * @return 西邮寄卡交易结算汇总表对象列表 *
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getMerchantDaySettlementSettleAccountsByMerchant(
			PCPSWebInput webInput, String merchantno, String startTime,
			String endTime) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(startTime) || PCPSUtil.isEmpty(endTime))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String merchantNoCond = "";
		if (!PCPSUtil.isEmpty(merchantno)) {
			merchantNoCond = " and merchantno='" + merchantno + "'";
		}
		String sql = "select a.merchantno,sum(a.Consume),sum(a.CancelPaid),sum(a.Consume-a.CancelPaid),sum(a.Deposit),sum(a.Paidfund),sum(0),sum(0),sum(a.Consumecount),sum(a.Consume-a.CancelPaid+0),sum(a.Paidfund+0) from MerchantDaySettlement a"
			+ " where a.IssueCompanyNo=?1  and a.TranDate>=to_date(?2,'yyyy-mm-dd') and a.TranDate<=to_date(?3,'yyyy-mm-dd') "
			+ merchantNoCond + " group by a.merchantno order by a.MERCHANTNO ";
		Query query = _em.createNativeQuery(sql);

		query.setParameter(1, issuecompanyno);
		query.setParameter(2, startTime);
		query.setParameter(3, endTime);

		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getMerchantDaySettlementCountReport(
			PCPSWebInput webInput, String startDate,String endDate, 
			String todayDate,String merchantNoStr) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())
				|| PCPSUtil.isEmpty(startDate) || PCPSUtil.isEmpty(endDate)
				|| PCPSUtil.isEmpty(todayDate) || PCPSUtil.isEmpty(merchantNoStr))
			return null;
		
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = " select '昨日累计',nvl(sum(ConsumeCount),0),nvl(sum(Consume+OtherPaid-CancelPaid)/100,0),nvl(sum(Consume-CancelPaid)/100,0), " +
			" nvl(sum(OtherPaid)/100,0) from  MerchantDaySettlement  where IssueCompanyNo= ?1 " +
			" and TranDate >= to_date(?2,'yyyy-mm-dd') and TranDate <= to_date(?3,'yyyy-mm-dd') " +
			" and MerchantNo in("+merchantNoStr+") union all " +
			" select '今日数量', nvl(sum(ConsumeCount),0),nvl(sum(Consume+OtherPaid-CancelPaid)/100,0),nvl(sum(Consume-CancelPaid)/100,0), " +
			" nvl(sum(OtherPaid)/100,0) from  MerchantDaySettlement  where IssueCompanyNo= ?1 " +
			" and TranDate = to_date(?4,'yyyy-mm-dd') and MerchantNo in("+merchantNoStr+") union all " +
			" select '今日累计', nvl(sum(ConsumeCount),0),nvl(sum(Consume+OtherPaid-CancelPaid)/100,0),nvl(sum(Consume-CancelPaid)/100,0), " +
			" nvl(sum(OtherPaid)/100,0) from  MerchantDaySettlement  where IssueCompanyNo= ?1 " +
			" and TranDate >= to_date(?2,'yyyy-mm-dd') and TranDate <= to_date(?4,'yyyy-mm-dd') " +
			" and MerchantNo in("+merchantNoStr+") ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, startDate);
		query.setParameter(3, endDate);
		query.setParameter(4, todayDate);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}
}